package DBLayer;
import ModelLayer.Country;
import java.sql.*;
import java.util.ArrayList;

public class DBCountry {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBCountry() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public Country getCountryByID(int id) {
        return selectSingle("SELECT `id_country`, `id_zone`, `iso_code`, `active`, `contains_states`, `name` FROM `ps_country` LEFT JOIN `ps_country_lang` USING (`id_country`) WHERE (`id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL) AND `id_country` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<Country> getCountryList() {
        return selectMultiple("SELECT `id_country`, `id_zone`, `iso_code`, `active`, `contains_states`, `name` FROM `ps_country` LEFT JOIN `ps_country_lang` USING (`id_country`) WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL ORDER BY `id_country` ASC;");
    }

    private Country selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        Country country = new Country();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                country = buildCountry(result);
            }
            System.out.println("Fetching Country Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return country;
    }

    private ArrayList<Country> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Country> countries = new ArrayList<Country>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                Country country = new Country();
                country = buildCountry(result);
                countries.add(country);
            }
            System.out.println("Fetching Country List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return countries;
    }

    private Country buildCountry(ResultSet result) {
        Country country = new Country();
        try {
            country.setIDCountry(result.getInt("id_country"));
            country.setIDZone(result.getInt("id_zone"));
            country.setIsoCode(result.getString("iso_code"));
            if (result.getInt("active") == 1) {
                country.setActive(true);
            } else {
                country.setActive(false);
            }
            if (result.getInt("contains_states") == 1) {
                country.setContainsStates(true);
            } else {
                country.setContainsStates(false);
            }
            country.setName(result.getString("name"));

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return country;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addCountry(Country country) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_country_lang` (`id_country`, `id_lang`, `name`) "
                + "VALUES (" + country.getIDCountry() + ", " + Constants.DEFAULT_LANGUAGE + ", '" + country.getName() + "');";

        query += "INSERT INTO `ps_country` (`id_country`, `id_zone`, `iso_code`, `active`, `contains_states`) "
                + "VALUES (" + country.getIDCountry() + ", " + country.getIDZone() + ", '" + country.getIsoCode() + "', ";
        if (country.getActive() == true)            { query += "1, "; }
        else                                        { query += "0, "; }
        if (country.getContainsStates() == true)    { query += "1);"; }
        else                                        { query += "0);"; }

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added Country ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateCountry(Country country, int id) {
        int returnID = 0;
        Statement stmt = null;
        String query = "UPDATE `ps_country` SET "
                + "`id_country`         = "  + country.getIDCountry() + ", "
                + "`id_zone`            = "  + country.getIDZone() + ", "
                + "`iso_code`           = '" + country.getIsoCode() + "', ";
        if (country.getActive() == true)            { query += "`active` = 1, "; }
        else                                        { query += "`active` = 0, "; }
        if (country.getContainsStates() == true)    { query += "`contains_states` = 1 "; }
        else                                        { query += "`contains_states` = 0 "; }
        query  += "WHERE `id_country` = " + id + " LIMIT 1; ";

        query += "UPDATE `ps_country_lang` SET "
                + "`id_country`         = "  + country.getIDCountry() + ", "
                + "`name`               = '" + country.getName() + "' "
                + "WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " AND `id_country` = " + id + " LIMIT 1;";
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = country.getIDCountry();
            System.out.println("Updated Country ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteCountry(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_country` WHERE `id_country` = " + id + " LIMIT 1; DELETE FROM `ps_country_lang` WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " AND `id_country` = " + id + " LIMIT 1;");
            System.out.println("Deleted Country ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
